create table EntityHeadquarter
( 
  ChainId int,
  Code varchar(10),
  Description varchar(50),
  ExtendedDescriotion varchar(4000),
  LastUpdated Date not null,
  UpdatedBy varchar(50)


)


create table Entity 
(
	EntityId int not null, 
	Description varchar(50) not null,
	ChainId int null,
	ExtendedDescriotion varchar(4000),
	LastUpdated Date not null,
	UpdatedBy varchar(50)
	
)


create table Producer
(
  ProducerId int,
  Code varchar(10),
  Description varchar(50),
  ExtendedDescriotion varchar(4000),
  Country varchar(50),
  LastUpdated Date not null,
  UpdatedBy varchar(50),

 
)

create table Distributor
(
  DistributorId int,
  Code varchar(10),
  Description varchar(50),
  ExtendedDescriotion varchar(4000),
  LastUpdated Date not null,
  UpdatedBy varchar(50)
 
)

create table ClassificationHierarchy
(
  ProductClassId int,
  Description varchar(50),
  MasterClassId int,
  ExtendedDescription varchar(4000),
  ClaasificationType int  ,
  LastUpdated Date not null,
  UpdatedBy varchar(50)
 
)


create table Product
(
	ProductId int not null, 
	ReferenceCode varchar(20),
	Description varvhar (255),
	ProducerId int,
	DistributorId int,
	ProductTypeId int not null,        -- claassification  1
	ProductSubTypeId int  null,
	ManufacturerClassId int  null,  -- claassification  2
	EnvironmentClassId int  null,   -- classification 3
	HealthClassId int  null,        -- classification 4
	AverageSalePrice decimal (11,2),
	AverageDealPrice decimal (11,2) ,
	AverageDealDays  int,
	barcode1  varchar(255),
	barcode2  varchar(255),
	pictureId1  int,
	pictureId2  int,
	lastUpdated Date not null,
    UpdatedBy varchar(50)

)

create table ChainEntityProducts
( 
  ChainId int null,   --either or
  EntityId iint null, --
  ProductId int,
  IsExclusive bit,
  LastUpdated Date not null,
  UpdatedBy varchar(50)


)

create table SelectedProducts
(
	SelectedProductsId int not null, 
	ProductTypeId int  null,        -- claassification  1
	ProductSubTypeId int  null,
	ManufacturerClassId int  null,  -- claassification  2
	EnvironmentClassId int  null,   -- classification 3
	HealthClassId int  null,        -- classification 4
  LastUpdated Date not null,
  UpdatedBy varchar(50)

)


create table PeopleProduct
(
	Id int not null,
	PersonId int not null, 
	ProductId int  null, 
	SelectedProductsId int null,   -- do selection not individual but by class
	SalePrice decimal (11,2) null,
	SaleDiscount decimal (5,2),
	TriggerForSpecialDeals bit,
    LastUpdated Date not null,
    UpdatedBy varchar(50)

)

create table SalePeople
(
	SaleId int not null, 
	PersonId int not null,
	ReadyForEmail bit,
	EmailSent bit,
    LastUpdated Date not null,
    UpdatedBy varchar(50) not null

)


--products
--chain
--entity


create table Sale
(
	SaleId  int not null, 
	CkainId  int null , -- either or 	
	EntityId int null,  --
	StartOn Date not null,
	EndsAt  Date not null, 
	NotificationsStartAt Date not null,  
	CreatedAt Date not null,
	CratedBy varchar(50),
	LastUpdated Date not null,
	UpdatedBy varchar(50)

)

create table ProviceStates
(
	Province_StateCode varchar(3) not null,
	Description varchar(3) not null
)






create table TargetArea
(
	TargetAreaId  int not null, 
	Province_State varchar(3) null,
	Postal_ZipCode varchar(6) null,
	City varchar(50) null,
	Region varchar(50) null, -- like fraser valley
	LastUpdated Date not null,
	UpdatedBy varchar(50)

)


create table SaleTargets
(
	SaleId int not null, 
	TargetAreaId int not null,
	LastUpdated Date not null,
	UpdatedBy varchar(50)

)



